{
 "cells": [
  {
   "cell_type": "code",
   "outputs": [],
   "source": [
    "import re\n",
    "import pandas as pd"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-06-16T12:07:40.619271800Z",
     "start_time": "2024-06-16T12:07:40.213378500Z"
    }
   },
   "id": "90577b712b56de7a",
   "execution_count": 1
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "initial_id",
   "metadata": {
    "collapsed": true,
    "ExecuteTime": {
     "end_time": "2024-06-16T12:07:40.911988500Z",
     "start_time": "2024-06-16T12:07:40.620272700Z"
    }
   },
   "outputs": [],
   "source": [
    "combined_df=pd.read_csv('all_data.csv')"
   ]
  },
  {
   "cell_type": "code",
   "outputs": [],
   "source": [
    "# 识别含有NaN值的行\n",
    "rows_with_nan = combined_df[combined_df.isnull().any(axis=1)]\n",
    "# 假设combined_df是你的DataFrame\n",
    "#去掉nan值\n",
    "combined_df.fillna(\"无\", inplace=True)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-06-16T12:07:55.601358600Z",
     "start_time": "2024-06-16T12:07:55.574357400Z"
    }
   },
   "id": "5f36b380eea4fb2d",
   "execution_count": 3
  },
  {
   "cell_type": "code",
   "outputs": [],
   "source": [
    "job_salary=combined_df['job_salary']"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-06-16T12:08:04.798185600Z",
     "start_time": "2024-06-16T12:08:04.780188900Z"
    }
   },
   "id": "b6d3f415f9a340a6",
   "execution_count": 4
  },
  {
   "cell_type": "code",
   "outputs": [
    {
     "data": {
      "text/plain": "8        120-150元/天\n10       120-150元/天\n11       150-180元/天\n12       120-150元/天\n14       120-150元/天\n            ...    \n12907     45-136元/天\n12908     45-136元/天\n12957    114-227元/天\n12958    114-227元/天\n13071    135-230元/天\nName: job_salary, Length: 1958, dtype: object"
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pattern_internship_day=r'\\d+元/天'\n",
    "job_internship_day=job_salary[job_salary.str.contains(pattern_internship_day)]\n",
    "job_internship_day"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-06-16T12:14:11.092568400Z",
     "start_time": "2024-06-16T12:14:11.064934700Z"
    }
   },
   "id": "4724749a5c872c0",
   "execution_count": 13
  },
  {
   "cell_type": "code",
   "outputs": [
    {
     "data": {
      "text/plain": "8917     100-150元/时\n8918     300-500元/时\n8924       25-50元/时\n8993     150-200元/时\n9007        5-20元/时\n            ...    \n11323      20-25元/时\n11345     50-100元/时\n11378      20-25元/时\n11380      30-35元/时\n11444      90-95元/时\nName: job_salary, Length: 63, dtype: object"
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pattern_internship_hour=r'\\d+元/时'\n",
    "job_internship_hour=job_salary[job_salary.str.contains(pattern_internship_hour)]\n",
    "job_internship_hour"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-06-16T12:17:24.204920500Z",
     "start_time": "2024-06-16T12:17:24.182385800Z"
    }
   },
   "id": "5d0ea038ba6a3d1d",
   "execution_count": 18
  },
  {
   "cell_type": "code",
   "outputs": [
    {
     "data": {
      "text/plain": "company_brief_address                                                南京玄武区长江路\ncompany_detailed_address                                     南京玄武区长江花园11号楼11号\nhr_name                                                                   钱海锋\njob_tags                                                    ['经验不限', '初中及以下']\njob_title                                                         南京鼎满公司招聘服务员\njob_salary                                                           15-25元/时\ncompany_name                                                           南京鼎满公司\ncompany_intro                                                               无\ncompany_status                                                              无\ncompany_size                                                           20-99人\ncompany_type                                                               餐饮\njob_description             工作周期：长期兼职\\r\\n每周工期：5天及以上\\r\\n工作时间：不限\\r\\n工作时段：不限\\...\nName: 11047, dtype: object"
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "combined_df.loc[11047]"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-06-16T12:22:48.021496700Z",
     "start_time": "2024-06-16T12:22:48.003377Z"
    }
   },
   "id": "140abbccaac30fd0",
   "execution_count": 28
  },
  {
   "cell_type": "code",
   "outputs": [
    {
     "data": {
      "text/plain": "0               20-40k\n1               10-15k\n3           25-40k·14薪\n4                6-18k\n5               20-50k\n             ...      \n13129     10.8k-15k元/月\n13130      8.3k-25k元/月\n13131      6.7k-10k元/月\n13132    8.3k-12.5k元/月\n13133      6.7k-10k元/月\nName: job_salary, Length: 10583, dtype: object"
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pattern_extract_1=r'\\b\\d{1,5}(?:-\\d{1,5})?[kK]\\b'\n",
    "job_salary_extract_1=job_salary[job_salary.str.contains(pattern_extract_1)]\n",
    "job_salary_extract_1"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-06-16T12:17:24.424929900Z",
     "start_time": "2024-06-16T12:17:24.389563500Z"
    }
   },
   "id": "3fb5f94696acfa1",
   "execution_count": 19
  },
  {
   "cell_type": "code",
   "outputs": [
    {
     "data": {
      "text/plain": "8940      6000-15000元/月\n8996      5000-10000元/月\n9124       3000-4500元/月\n9130       3500-4500元/月\n9382     14000-15000元/月\n9388       4000-8000元/月\n9509       3000-6000元/月\n9524     14000-15000元/月\n9605       1500-3000元/月\n9747       2500-3500元/月\n9768       4000-7000元/月\n9902       4000-7000元/月\n9949       4500-5500元/月\n9953     13000-14000元/月\n10154      3000-4000元/月\n10238    13000-15000元/月\n10320     5000-15000元/月\n10357      3000-6000元/月\n10453      2000-2500元/月\n10897      1500-2000元/月\nName: job_salary, dtype: object"
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pattern_extract_2=r'\\b\\d{1,5}(?:-\\d{1,5})+元/月\\b'\n",
    "job_salary_extract_2=job_salary[job_salary.str.contains(pattern_extract_2)]\n",
    "job_salary_extract_2"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-06-16T12:19:00.425358900Z",
     "start_time": "2024-06-16T12:19:00.393530400Z"
    }
   },
   "id": "a9e6cea359d3693",
   "execution_count": 21
  },
  {
   "cell_type": "code",
   "outputs": [
    {
     "data": {
      "text/plain": "job_salary\n面议            264\n薪资面议          220\n无              13\n120000元/月       3\n700000元/月       2\njob_salary      2\n150000元/月       1\n80000元/月        1\n100-200元/周      1\n4-7元/单          1\n300-400元/周      1\n800-15k元/月      1\nName: count, dtype: int64"
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 使用~操作符取反，找到不符合上述两个条件的数据\n",
    "other_salaries = job_salary[~job_salary.str.contains(pattern_extract_1) & ~job_salary.str.contains(pattern_internship_day)& ~job_salary.str.contains(pattern_internship_hour)& ~job_salary.str.contains(pattern_extract_2)]\n",
    "other_salaries.value_counts()"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-06-16T12:19:24.356448900Z",
     "start_time": "2024-06-16T12:19:24.319864100Z"
    }
   },
   "id": "3b931ba662b1b065",
   "execution_count": 22
  },
  {
   "cell_type": "markdown",
   "source": [
    "主要分析以下数据"
   ],
   "metadata": {
    "collapsed": false
   },
   "id": "9f88d20b445f8454"
  },
  {
   "cell_type": "code",
   "outputs": [
    {
     "data": {
      "text/plain": "0               20-40k\n1               10-15k\n3           25-40k·14薪\n4                6-18k\n5               20-50k\n             ...      \n13129     10.8k-15k元/月\n13130      8.3k-25k元/月\n13131      6.7k-10k元/月\n13132    8.3k-12.5k元/月\n13133      6.7k-10k元/月\nName: job_salary, Length: 10583, dtype: object"
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pattern_extract_1=r'\\b\\d{1,5}(?:-\\d{1,5})?[kK]\\b'\n",
    "job_salary_extract_1=job_salary[job_salary.str.contains(pattern_extract_1)]\n",
    "job_salary_extract_1"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-06-16T12:23:25.570156400Z",
     "start_time": "2024-06-16T12:23:25.540843400Z"
    }
   },
   "id": "7cc6aaf52557192e",
   "execution_count": 29
  },
  {
   "cell_type": "code",
   "outputs": [
    {
     "data": {
      "text/plain": "3        25-40k·14薪\n13        5-10k·13薪\n18       18-25k·14薪\n23        8-15k·14薪\n33       20-25k·13薪\n            ...    \n11465     5-10K·14薪\n11467    13-18K·13薪\n11472      6-9K·13薪\n11474     5-10K·14薪\n11475    14-16K·14薪\nName: job_salary, Length: 1682, dtype: object"
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pattern_extract_13=r'\\b\\d{1,5}(?:-\\d{1,5})?[kK]·\\d{1,2}薪\\b'\n",
    "pattern_extract_13=job_salary[job_salary.str.contains(pattern_extract_13)]\n",
    "pattern_extract_13"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-06-16T12:27:05.471520700Z",
     "start_time": "2024-06-16T12:27:05.458008400Z"
    }
   },
   "id": "1daeddee3e586b2b",
   "execution_count": 30
  },
  {
   "cell_type": "code",
   "outputs": [
    {
     "data": {
      "text/plain": "0               20-40k\n1               10-15k\n4                6-18k\n5               20-50k\n6               30-45k\n             ...      \n13129     10.8k-15k元/月\n13130      8.3k-25k元/月\n13131      6.7k-10k元/月\n13132    8.3k-12.5k元/月\n13133      6.7k-10k元/月\nName: job_salary, Length: 8901, dtype: object"
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pattern_extract_12=r'\\b\\d{1,5}(?:-\\d{1,5})?[kK]\\b(?!\\s*·)'\n",
    "pattern_extract_12=job_salary[job_salary.str.contains(pattern_extract_12)]\n",
    "pattern_extract_12"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-06-16T12:32:40.534320900Z",
     "start_time": "2024-06-16T12:32:40.508249Z"
    }
   },
   "id": "d85769f57828cc82",
   "execution_count": 34
  },
  {
   "cell_type": "code",
   "outputs": [
    {
     "data": {
      "text/plain": "job_salary\n10-15k         336\n5-8k           282\n8-12k          271\n5-10k          262\n5-10K          174\n              ... \n4k-80k元/月        1\n4k-30k元/月        1\n13-29k           1\n50k-100k元/月      1\n5.7-6.2K         1\nName: count, Length: 585, dtype: int64"
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pattern_extract_12.value_counts()"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-06-16T12:37:24.782788600Z",
     "start_time": "2024-06-16T12:37:24.768810300Z"
    }
   },
   "id": "4a1ff8b667098e7e",
   "execution_count": 37
  },
  {
   "cell_type": "code",
   "outputs": [],
   "source": [
    "# 定义函数来提取数字并计算\n",
    "def calculate_salary(salary_str):\n",
    "\n",
    "    # 移除元/月，并统一处理k为千的单位\n",
    "    salary_str = salary_str.replace(\"元/月\", \"\").replace(\"K\", \"\").replace(\"k\", \"\")\n",
    "    \n",
    "    # 处理只有下限或只有上限的情况，例如 \"3-6K\" 或 \"6-11K\"\n",
    "    if \"-\" not in salary_str:\n",
    "        salary_parts = salary_str.split()\n",
    "        if len(salary_parts) == 1:  # 只有一个数字，假设为下限\n",
    "            lower, upper = float(salary_parts[0]), float(salary_parts[0])\n",
    "        else:  # 两个独立的数字，分别作为下限和上限\n",
    "            lower, upper = float(salary_parts[0]), float(salary_parts[1])\n",
    "    else:  # 一般情况，包含\"-\"的区间\n",
    "        lower, upper = map(float, salary_str.split(\"-\"))\n",
    "    if float(((upper - lower) / 2) * 12)<1:\n",
    "        print(salary_str)\n",
    "    \n",
    "    # 计算平均年薪\n",
    "    return float(((upper + lower) / 2) * 12)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-06-16T13:03:44.079118400Z",
     "start_time": "2024-06-16T13:03:44.063562600Z"
    }
   },
   "id": "926f107dda0e22f7",
   "execution_count": 63
  },
  {
   "cell_type": "code",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "4.2-4.3\n",
      "7.9-8\n",
      "4.9-5\n",
      "4-4\n",
      "15-15\n",
      "6-6\n"
     ]
    }
   ],
   "source": [
    "annual_salary=pattern_extract_12.apply(calculate_salary)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-06-16T13:03:44.525109200Z",
     "start_time": "2024-06-16T13:03:44.507085900Z"
    }
   },
   "id": "687e6b26a34c9b00",
   "execution_count": 64
  },
  {
   "cell_type": "code",
   "outputs": [
    {
     "data": {
      "text/plain": "0        360.0\n1        150.0\n4        144.0\n5        420.0\n6        450.0\n         ...  \n13129    154.8\n13130    199.8\n13131    100.2\n13132    124.8\n13133    100.2\nName: job_salary, Length: 8901, dtype: float64"
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "annual_salary"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-06-16T13:03:45.021993400Z",
     "start_time": "2024-06-16T13:03:45.007888700Z"
    }
   },
   "id": "fe92e584dfb548fb",
   "execution_count": 65
  },
  {
   "cell_type": "code",
   "outputs": [],
   "source": [
    "# 定义函数来提取数字并计算\n",
    "def calculate_salary_num(salary_str):\n",
    "    match = re.search(r'(\\d+-\\d+)', salary_str)\n",
    "    salary=match.group(1)\n",
    "    # print(salary)\n",
    "    match_num = re.search(r'(\\d+)薪', salary_str)\n",
    "    salary_num=int(match_num.group(1))\n",
    "    # print(salary_num)\n",
    "    salary_str = salary\n",
    "\n",
    "    # 处理只有下限或只有上限的情况，例如 \"3-6K\" 或 \"6-11K\"\n",
    "    if \"-\" not in salary_str:\n",
    "        salary_parts = salary_str.split()\n",
    "        if len(salary_parts) == 1:  # 只有一个数字，假设为下限\n",
    "            lower, upper = float(salary_parts[0]), float(salary_parts[0])\n",
    "        else:  # 两个独立的数字，分别作为下限和上限\n",
    "            lower, upper = float(salary_parts[0]), float(salary_parts[1])\n",
    "    else:  # 一般情况，包含\"-\"的区间\n",
    "        lower, upper = map(float, salary_str.split(\"-\"))\n",
    "    return float(((upper + lower) / 2) * salary_num)\n"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-06-16T13:17:01.580542100Z",
     "start_time": "2024-06-16T13:17:01.562943600Z"
    }
   },
   "id": "2892d8ce9762228e",
   "execution_count": 78
  },
  {
   "cell_type": "code",
   "outputs": [],
   "source": [
    "annual_salary_num=pattern_extract_13.apply(calculate_salary_num)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-06-16T13:17:02.430678Z",
     "start_time": "2024-06-16T13:17:02.392776500Z"
    }
   },
   "id": "f822fa3d09c5c5a5",
   "execution_count": 79
  },
  {
   "cell_type": "code",
   "outputs": [
    {
     "data": {
      "text/plain": "3        455.0\n13        97.5\n18       301.0\n23       161.0\n33       292.5\n         ...  \n11465    105.0\n11467    201.5\n11472     97.5\n11474    105.0\n11475    210.0\nName: job_salary, Length: 1682, dtype: float64"
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "annual_salary_num"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-06-16T13:17:05.385248100Z",
     "start_time": "2024-06-16T13:17:05.367321200Z"
    }
   },
   "id": "893b0b722b3ec932",
   "execution_count": 80
  },
  {
   "cell_type": "code",
   "outputs": [],
   "source": [
    "all_annual_salary=pd.concat([annual_salary,annual_salary_num], axis=0)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-06-16T13:18:19.418498600Z",
     "start_time": "2024-06-16T13:18:19.405481Z"
    }
   },
   "id": "c0cc28ae89f1f0d5",
   "execution_count": 81
  },
  {
   "cell_type": "code",
   "outputs": [],
   "source": [
    "combined_df['annual_salary']=all_annual_salary"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-06-16T13:21:18.294890700Z",
     "start_time": "2024-06-16T13:21:18.271214500Z"
    }
   },
   "id": "baef6d0f03f385a",
   "execution_count": 83
  },
  {
   "cell_type": "code",
   "outputs": [],
   "source": [
    "combined_df.to_csv('all_data_annual_salary.csv', index=False)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-06-16T13:22:27.968614100Z",
     "start_time": "2024-06-16T13:22:27.742098700Z"
    }
   },
   "id": "2aba97c5633cb069",
   "execution_count": 85
  },
  {
   "cell_type": "code",
   "outputs": [
    {
     "data": {
      "text/plain": "0        360.0\n1        150.0\n4        144.0\n5        420.0\n6        450.0\n         ...  \n11465    105.0\n11467    201.5\n11472     97.5\n11474    105.0\n11475    210.0\nName: job_salary, Length: 10583, dtype: float64"
     },
     "execution_count": 82,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_annual_salary"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-06-16T13:18:21.955265400Z",
     "start_time": "2024-06-16T13:18:21.929232600Z"
    }
   },
   "id": "74528ea6e750ddd4",
   "execution_count": 82
  },
  {
   "cell_type": "code",
   "outputs": [],
   "source": [],
   "metadata": {
    "collapsed": false
   },
   "id": "eec8d8e3c4b4cf5d"
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
